Spreadsheets enable the organization of data into columns and rows, permitting rapid accumulation of information by a user. Before spreadsheet processors were developed for computers, spreadsheet information, especially financial, was prepared on accountants' columnar pads or paper spreadsheets, using pencils and calculators. The task of preparing a spreadsheet on paper is much slower than in a computer because each entry must be calculated and hand entered in the spreadsheet. Manually prepared spreadsheets are prone to errors.
A spreadsheet processor (hereafter, spreadsheet) is an interactive computer processor that consists of a collection of rows and columns displayed on a computer monitor screen in a scrollable window. The intersection of each row and column is called a cell or a node. A cell can hold a number, a text string, or a formula that does calculations using the entries in one or more other cells. A spreadsheet can copy cells, move cells, and modify formulas. Spreadsheets may save a cell in a file for later use and may discard a cell after it has served its purpose. Spreadsheets can format cells in a spreadsheet presentation in a variety of ways and the presentation may be printed for hard-copy reference. In addition, groups of cells can be used to generate charts and maps.
Perhaps the most significant advantage of an electronic spreadsheet is the easy, immediate recalculation of a formula's results when a change is made in any of the entries in cells. Once a spreadsheet is set up by defining formulas, it can be used as a model to try different possibilities by varying entries to show the effect of changes.
A typical spreadsheet processor configures the memory of a computer to resemble the grid format of an accountant's columnar pad, providing a visible presentation for a user. Because the pad exists dynamically in the computer's memory, however, it differs from paper pads in several important ways. Locations in the electronic spreadsheet, for example, must be expressed to the computer in a format which it can use. A common scheme for accomplishing that is to assign a number to each row and a letter to each column. For example, the reference A1 designates the address of a location at column A and row 1, i.e., the cell in the upper-left-hand corner. In that manner, the spreadsheet reference defines an addressable storage location or cell at each intersection of a row with a column.
Data entry into an electronic spreadsheet is done in much the same manner that information is entered on an accountant's pad. A screen cursor is positioned at a desired location and a user enters alphanumeric information. Besides holding text and numeric information, however, spreadsheet cells can store special instructions such as formulas specifying calculations to be done on values stored in other spreadsheet cells. Cell addresses can be used as variables in an equation, allowing mathematical relationships to be defined among cells. The structure and operation of a spreadsheet processor, including functions and macros, are well known in the art.
Electronic spreadsheets offer many advantages. They can be much larger to hold more information than their paper counterparts; electronic spreadsheets having thousands or even millions of cells are not uncommon. Spreadsheet processors also allow users to do what-if scenarios. After they have stored a set of computational relationships into a worksheet, the spreadsheet information can be recalculated using different sets of assumptions with the results of each recalculation appearing almost instantaneously. Doing this operation manually with paper and pencil might require recalculating every relationship in the model with each change made.
Spreadsheets can be multidimensional rather than being restricted to two-dimensional models. In a three-dimensional spreadsheet, they uniquely identify each cell by a row, a column, and a page. This is analogous to a notebook or workbook. A notebook can hold any number of spreadsheets or worksheet pages. A notebook, therefore, depicts a three-dimensional representation of information cells. Using this metaphor, the user can leaf through many pages of information or to go to a specific page of information within the notebook.
The present state-of-the-art electronic spreadsheets have limitations and do not support additional forms of analysis that many users would find very valuable. For example, in current art spreadsheets, a value entered into a formula cell will override the formula, place the value in the cell, erase the formula, and invalidate the existing model logic. This result is counter productive. The spreadsheet should respond in a more intelligent, context-sensitive, fashion. First, it should not simply erase the formula and invalidate the model without further interaction with the user. Second, it should seek to correctly satisfy the user. The correct response when the user enters a value on a formula cell is to change the model's form, i.e., from one that cannot be calculated into one that can be calculated. Since many cells can contain formulas, the correct response will also include the ability for the model to take on any one of many possible forms. It should accept an entry into any formula cell present in the model, without breaking it.
It is an object of the present invention to both prevent a corrupt model and to provide a correct response when the user enters a value on a formula cell. The spreadsheet should generate one of a potentially unlimited number of new model forms that can be correctly calculated. The result is an event driven, polymorphic spreadsheet, that can reconfigure itself into one of several model formats dynamically during runtime operation.
Current art spreadsheets bind data and formulas together. When a model is loaded, it replaces both the current data and the current formulas in computer memory. This limits the user to a single spreadsheet model that operates on a single set of spreadsheet data. Commercially available spreadsheets at the current time also provide only a single standard calculation method that operates on a single model and a single set of data. Thus, the model's logic and data are bound together. It is, however, an advantage to be able to apply more than one model version to a single set of spreadsheet data. There are many desirable, but currently unsolved forms of analysis, that require the ability to apply multiple model versions to a single set of data.
Therefore, it is an object of the invention to uncouple spreadsheet data from its formulas, so that the data and the model formulas are independent. In so doing, an unlimited number of model versions can be applied to a single set of spreadsheet data.
U.S. Pat. No. 5,339,410 shows the use of more than a one-way calculation method. However, its bidirectional method is limited in scope to a forward and a backward direction. In a complex spreadsheet model with a large number of interrelated formula cells, a plurality of directions, i.e., combinations and permutations, exist. At every formula node directional decisions can be made that are equal in number to the number of precedent cells contained in the cell's formula. So, while many directions can exist for each cell, an almost unlimited number of directions can result from the combinations and permutations produced by many formula cells. Unfortunately, the bidirectional method is limited to a single directional decision for each cell. Also, since a bidirectional computation method is fixed in computer logic it is limited to a single alternate computational path. For each cell affected by a first change event, it derives a single inverse formula, using algebra, that it uses to calculate a single result cell. As a result, for any particular one of the model's formula cells, it can calculate only one alternate computational path. However, many alternate computational paths exist for each formula cell, i.e., one for each precedent cell found in the cell's formula. Therefore, the need exists to be able to select any one of the formula's precedent cells as the target cell and develop an inverse formula for that cell.
There is also a need for the user to control the computations. Every formula cell is a node in the spreadsheet that can make a directional decision. However, no method is currently provided for the user to control and to select computational permutations with multiple directions, both statically at design-time and dynamically at runtime. Both design-time and runtime interactive directional control require an intelligent, interactive cell type. The cells need the capability to interact with the user giving the user a list of options. The user must be enable to select from those options, and the cells must be able to correctly perform the selected option.
In view of the foregoing, it is an object of the present invention to provide an interactive cell type where directional decisions can be made for every node. Such decisions can be made in formula nodes and also in independent trigger nodes. Such decisions can be made both statically at design-time and dynamically at runtime. The invention provides directional decision making for a limitless number of computational directions.
The need exists for handling formulas that cannot be inverted such as non-algebraic formulas that contain functions or constructs using the logical values of cells. Also, even when a formula is an algebraic equation, there remain many complex forms that cannot be inverted.
It is, therefore, an object of the present invention to eliminate fatal errors resulting from total reliance upon the derivation of an inverse formula; to provide a means for user input of impact formulas, values or other acceptable cell contents; to provide a means for accessing and using user provided content in the model permutation process, and simultaneously providing enhancements to the inverse formula process so that it can be of use where it is appropriate and only where it is successful.
It is a further object of the present invention to eliminate the need for right-side to left-side value comparisons of bi-directional equations to eliminate fatal errors resulting from floating point inequalities. This is accomplished with the polymorphic focus of the present invention. When a model is permutated from one form that cannot be calculated into another form that can be calculated, the result is a standard spreadsheet calculation of a valid spreadsheet model. As a consequence, there is no right-side nor left-side to evaluate what-so-ever in the permutated model.
An independent cell is a cell that is not found in the formula of a formula cell. The independent cell, however, is one that in certain cases should be changed when the formula cell changes value. In the current art there are no means to detect the existence of an independent cell from the formula cell. It is an object of the present invention to provide an impact cell that can effect a change to an independent cell when needed.
There is a need for a multiple impact cell, one where a change to a formula cell will result in a change to more than one direct or indirect precedent cell and/or independent cells. It is an object of the present invention to provide for multiple impact cells that can impact a plurality of precedent and independent cells with various forms of contents during model permutation events.
A special case of the multiple impact cell is the multiple allocation cell which enables an allocation of an amount entered into a multiple impact cell among the precedent cells. Such allocation may be equal to all cells, based upon a percentage calculated from the previous value as a percent of the total value, or the amount based upon weighting factors, such as seasonal sales factors found in a separate range of cells within the spreadsheet. There is no provision for multiple allocation cells in current art spreadsheets.
It is an object of the present invention to provide multiple allocation impact cells, including but not limited to split even, split percent, split using weights stored in a named range of cells, or split to a partial list of precedent cells.
There are special cases involving multiple impact and multiple allocation cells where precedent cells require group processing. Because each of the children cells receive part of their parent cell's value, they require interdependent processing to maintain model synchronization so that if any one of the group of sibling cells subsequently change content, the parent and all of the sibling cells receive notification and change instructions. Interdependent sibling cells are not provided in current art spreadsheets. It is an object of the present invention to provide for interdependent impact cells for the grouping and callback processing required for proper model synchronization as the result of multiple impact parent cells.
There is also a need for the user to be able to provide independent processing logic to create permutations in the model which may take the form of an algorithm beyond the scope of standard processing provided by the spreadsheet or its formulas. Therefore, the need exists for an algorithm control mechanism to provide for alternative forms of processing as defined by the user. In that manner, user defined subroutines can satisfy the unlimited range of computational results that a user may want.
It is an object of the present invention to provide for user defined subroutines in the form of independent processing logic that can be stored and referenced by any impact cell type to provide unlimited computational alternatives that may go beyond the scope of standard spreadsheet operations.
The bidirectional method mentioned above is limited to triggering its single alternate computational method by formula type cells. However, it is also advantageous to have the capability of storing a permutation trigger within an input type cell. In the present invention, provision is made for a permutation trigger. That trigger consists of cell contents that are passed to an impact cell when data is entered into the input cell. This action results in an additional process beyond a simple edit to the value of an input cell. It results in an additional model permutation, i.e., an action, triggered in an independent cell, that can impact one or more additional cells. There is no provision for an independent cell containing a permutation trigger in current art spreadsheets.
It is a further object of the present invention to provide independent impact cells with permutation triggers (independent trigger cells) that can impact or effect changes in one or more other model cells while simultaneously functioning as an input cell.
In the current art, a model is loaded into the spreadsheet, calculations are performed, and the model always returns to the same original form. However, it is advantageous in many circumstances to not return to the original model form. In fact, it is more advantageous to be able to assume many model forms, i.e., not just the starting model form, but a series of many model forms, as many possible forms as may be derived from the combinations and permutations present in the starting model equations. Thus, an accumulation of changes can occur so that the resulting model may be quite different from the model loaded by the user, while requiring little or no additional input from the user. There is, however, no provision in current art spreadsheets for model permutations of this kind.
It is an object of the present invention to provide incremental or cumulative model permutations to accommodate every possible combination and permutation of form that a model can take. And further, to provide for model versions that are independent of all current cell content so that unlimited possibilities can exist for model permutation and the application of a plurality of models to a single set of spreadsheet data.
It is a further object of the present invention to support undo options such as, reset after change, no reset after change, i.e., so that incremental changes can accumulate, reset to the original model, or incremental undo of changes to retrace steps. While the undo of edits made by the user in the current art will undo only the individual edits made, undo in a permutation enhanced spreadsheet will also undo the permutations made to the model as directed by the user.
Current art spreadsheets enable users to copy cells, move cells, and modify formulas. The relative cell references that are found within the cell content is maintained during those operations. However, because the current art spreadsheets are not permutation-enabled there are many operations that will fail to produce the correct cell content when a model permutation occurs. For example, a cells' permutation content is not maintained for clipboard operations, multiple sheet selections, drag-copy or drag-move operations, insertions or deletions of cell(s), row(s), column(s), and sheet(s), and renaming model components. The polymorphic spreadsheet cell is permutation-enabled, and therefore, the inability of current art spreadsheets to maintain its permutation content during content transfer or move operations is overcome.
Therefore, it is an object of the present invention to "permutation-enable" every operation that transfers or moves cell content, i.e., not only for the original content, but also the polymorphic content used in model permutation operations.
Current art spreadsheets provide built-in functions such as sum and average, as well as more complex functions that perform sophisticated operations such as the correlation coefficient for two sets of data. However, these built-in functions are not permutation-enabled. Such functions may not properly operate when used in a permutation-enabled cell. For example, the simple sum command may become an impact targeting mechanism for a plurality of cells in a model permutation. Therefore, it is an object of the present invention to provide permutation-enabled functions so that such functions can change from one form into another form, for interacting with the permutation behaviors of the invention to yield valid alternative model forms.
Current art spreadsheets are available in multiple delivery formats, i.e., in a standalone form and in a component form. The component form encapsulates the behaviors of the spreadsheet and provides a callable public interface to the operations contained in the component. However, because current art spreadsheet components are not polymorphic, their callable interface does not include operations that can correctly interface with the permutation enabled methods of a polymorphic spreadsheet. Therefore, it is an object of the present invention to provide a component format that is permutation enabled with user callable interface routines. These interface routines provide polymorphic operations that interface with the component's data structure and are beyond the capabilities of the current art.
Formulas are the quintessential spreadsheet capability that perform the analysis that makes them so useful. Formulas are entered into the spreadsheet cell using a format that can be parsed by the spreadsheet and translated into a form that can be calculated in computer memory. The format of the spreadsheet formula however, is inadequate to provide essential permutation content to the cell. Therefore, it is an object of the invention to provide an enhanced formula syntax, i.e., a syntax that can be parsed by the polymorphic spreadsheet into current and future cell content or other permutation control behaviors as are necessary to achieve both current cell calculation and future cell permutation.
Circular references are managed in current art spreadsheets via an iteration control with settings for the maximum number of iterations or a maximum change value. However, the circular reference of a cell or its precedents or indirect precedents referring to itself in a formula takes on new meaning when that formula is permutated or inverted. There are two distinctions that can be made. First, the formula in a permutated form may contain a circular reference. The iteration control of the current art is adequate to handle the computational iterations of that reference. Second, the sequence of cell permutations that can occur when the inverse content is moved between cells, can lead to a permutation loop. In a permutation loop, the content that is moved out of the cell gets moved back into the cell. A permutation loop is a different event from a circular reference because it involves content, not computation. Current art spreadsheets do not provide a means for handling the permutation loop. An object of the current invention is to handle permutation loops.
Optimization and constraint processing in spreadsheets is most often achieved using a solver-add-in capability. The solver-add-in is a separate computational engine that is fixed in computer programming logic and takes a limited set of input from the user. With the single exception of a data entry validation rule, the user is not able to enter constraints directly into the spreadsheet cells. The user is given limited control over the methods of the computational processing. Because the solver technology was built using the single model paradigm it is not adequate for a polymorphic paradigm. The polymorphic paradigm enables a new approach to the user interface with constraint and optimization processing. First, the user should be able to enter constraints directly into the cells in a context sensitive fashion. Second, the user should be given access to and control over the model permutation behaviors that accomplish the constraint processing. The result of an optimization or constraint process is simply the reallocation of values among cells. The restricted access to the methods used for this allocation by the solver-add-in is a significant limitation that is removed in a polymorphic paradigm. It is an object of this invention to provide a polymorphic spreadsheet enabling the user to enter constraints directly into the cells and to control constraint processing.
Current art spreadsheets and spreadsheet components do not support a polymorphic paradigm. Therefore, the component interface of current art does not have polymorphic interaction with a polymorphic data structure. The eventuality of permutation content as a part of a cell formula syntax is also a short coming. The functions used in equations are unaware of their meaning in the context of permutations and polymorphic behaviors. The eventuality of permutation loops found in the polymorphic paradigm are not addressed. Current art spreadsheets do not support in-place editing of constraints, nor user access to control the form or sequence of processing for the allocations that occur during constraint processing.
Current art techniques do not anticipate, nor do they provide for interactive impact cells, cell selection for multiple single impact cell computational pathways, independent impact cells, multiple impact cells, multiple allocation cells, interdependent impact cells, user-provided algorithms that can be triggered by context-sensitive cell-based events, independent trigger cells, nor cumulative model permutations.
Current art techniques do not address the issue of applying an unlimited number of independent model versions to a single set of spreadsheet data, nor do they permit the user to have direct access and control over model permutation. In fact, they offer no permutation at all. They suffer from fatal errors that invalidate the model when a value is entered on a formula cell and lack the intelligence to respond in an intelligent, context-sensitive manner with a permutated model format.
The above shortcomings are avoided in the present invention by providing more than one mode of operation in combination with the ability to operate on a single spreadsheet's data with an unlimited number of alternative models.
The user is given direct access to the cell's enhanced data structure where each display cell can store more than one processing cell in computer memory as well as mode control, model permutation control and constraint algorithms. Each cell is enhanced so that it can simultaneously support a plurality of cell behaviors or functions to provide advanced capabilities for model permutation.
The user is provided with a hierarchy of impact cell types that support a plurality of model permutation behaviors that result in a substantially unlimited range of model permutation and computational results.
The result is a one-to-many spreadsheet that is polymorphic, i.e., can change from one form into another one of many alternative forms. Thus, a broader spectrum of modeling solutions is available while maintaining the speed and ease of the familiar spreadsheet human interface.